package modelovectorial.persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import modelovectorial.DocumentoPDF;
import soporte.MySQLConnexion;
import soporte.web.Pagination;
import utn.frc.tsb.utils.ValerioFritelli.HashTable.HashList;
import utn.frc.tsb.utils.ValerioFritelli.SimpleList.SimpleList;

/**
 * @project DLC Final / UTN FRC 2013
 * @date Febrero de 2013
 *
 * @author Gonzalo Arribere
 * @author Gustavo Morales
 * @author Pablo Moreno
 *
 * Esta clase se encarga de todas las operaciones a la base de datos que estan
 * relacionadas con los documentos pdf.
 */
public class DocumentoDB {

    private final static int MAX_WORDS_WITHOUT_INDEX_RADIUS = 5;//Maximo numero de documentos para las sugerencias.
    private final static String DOCUMENTS_TOTAL = "SELECT COUNT(*) AS total FROM documento ";
    private final static String DOCUMENTS_GET = "SELECT * FROM documento WHERE idDocumento IN (?) ";
    private final static String DOCUMENT_WORDS = "SELECT t.termino, i.indice "
            + " FROM indices AS i, terminosucio AS t "
            + " WHERE i.idTerminoSucio = t.idTerminoSucio "
            + "     AND i.indice >= ? "
            + "     AND i.idDocumento = ? "
            + "ORDER BY i.indice ASC "
            + "LIMIT ? ";
    private final static String GET_LAST_DOCUMENTOS = ""
            + "SELECT * FROM documento "
            + "ORDER BY idDocumento DESC "
            + "LIMIT 10 ";
    private final static String BROWSE_DOCUMENTOS = ""
            + "SELECT * FROM documento "
            + "ORDER BY idDocumento ASC "
            + "LIMIT ?, 10 ";
    private final static String GET_DOCUMENTO = ""
            + "SELECT * "
            + "FROM documento "
            + "WHERE idDocumento = ?";
    private final static String GETDOCUMENTS_MIN_INDEX = ""
            + "SELECT i.idDocumento, MIN(i.indice) as indice "
            + "FROM "
            + "   termino AS t, indices AS i, terminosucio AS s "
            + "WHERE "
            + "   i.idTerminoSucio = s.idTerminoSucio AND "
            + "   i.idDocumento IN (i?) AND "
            + "   s.idTermino = t.idTermino AND "
            + "   t.terminoLimpio IN (w?) "
            + "GROUP BY i.idDocumento "
            + "ORDER BY i.idDocumento ASC  ";

    /**
     * Devuelve un documento a partir de un id de documento.
     *
     * @param pIdDocument entero que contiene el id del documento a buscar
     * @return un objeto Documento que sera null en caso de no encontrarlo en la
     * base.
     */
    public static DocumentoPDF readDocument(int pIdDocument) {
        DocumentoPDF results = null;

        Connection connection = MySQLConnexion.getConnexion();
        ResultSet result;

        try {
            PreparedStatement preparedStatment = connection.prepareStatement(GET_DOCUMENTO);
            preparedStatment.setInt(1, pIdDocument);

            result = preparedStatment.executeQuery();

            if (result.next()) {
                results = new DocumentoPDF(result.getInt("idDocumento"),
                        result.getString("nombreDocumento"),
                        result.getString("autor"),
                        result.getString("ruta"),
                        result.getInt("cantidadDePaginas"),
                        result.getInt("maxTF"),
                        result.getInt("tamañoEnBytes"),
                        true);
            }
            result.close();
        } catch (Exception e) {
            System.out.println(e.getStackTrace());
        }
        return results;
    }

    public static DocumentoPDF[] browseDocuments(Pagination pPagination) {
        DocumentoPDF[] results = new DocumentoPDF[10];

        Connection connection = MySQLConnexion.getConnexion();
        ResultSet result;

        try {
            PreparedStatement preparedStatment = connection.prepareStatement(BROWSE_DOCUMENTOS);
            preparedStatment.setInt(1, pPagination.getInitIndex());

            result = preparedStatment.executeQuery();

            int index = 0;
            while (result.next()) {
                results[index] = new DocumentoPDF(result.getInt("idDocumento"),
                        result.getString("nombreDocumento"),
                        result.getString("autor"),
                        result.getString("ruta"),
                        result.getInt("cantidadDePaginas"),
                        result.getInt("maxTF"),
                        result.getInt("tamañoEnBytes"),
                        true);
                index++;
            }

            result.close();

            for (int i = 0; i < index; i++) {
                results[i].setMinIndex(0);
                results[i].fillTerms(true);
            }
            if (index != results.length) {
                DocumentoPDF[] realResult = new DocumentoPDF[index];
                System.arraycopy(results, 0, realResult, 0, index);
                results = realResult;
            }
        } catch (Exception e) {
            System.out.println(e.getStackTrace());
        }
        return results;
    }

    public static DocumentoPDF[] getLastDocuments() {
        DocumentoPDF[] results = new DocumentoPDF[10];

        Connection connection = MySQLConnexion.getConnexion();
        ResultSet result;

        try {
            PreparedStatement preparedStatment = connection.prepareStatement(GET_LAST_DOCUMENTOS);

            result = preparedStatment.executeQuery();

            int index = 0;
            while (result.next()) {
                results[index] = new DocumentoPDF(result.getInt("idDocumento"),
                        result.getString("nombreDocumento"),
                        result.getString("autor"),
                        result.getString("ruta"),
                        result.getInt("cantidadDePaginas"),
                        result.getInt("maxTF"),
                        result.getInt("tamañoEnBytes"),
                        true);
                index++;
            }

            result.close();

            for (int i = 0; i < results.length; i++) {
                results[i].setMinIndex(0);
                results[i].fillTerms(true);
            }
        } catch (Exception e) {
            System.out.println(e.getStackTrace());
        }
        return results;
    }

    public static int getTotalDocuments() {
        Connection connection = MySQLConnexion.getConnexion();
        ResultSet result;
        int count = 0;

        try {
            result = connection.createStatement().executeQuery(DOCUMENTS_TOTAL);
            if (result.next()) {
                count = result.getInt("total");
            }
            result.close();
            //connection.close ();
        } catch (Exception e) {
            System.out.println(e.getStackTrace());
        }
        return count;
    }

    /**
     * Obtiene todas las palabras de un documento y las concatena en un String
     * para formar el fragmento de muestra asociado al texto.
     *
     * @param pDocument Documento del que se buscaran las palabras
     * @param pUseRadiusIndex
     */
    public static void fillTerms(DocumentoPDF pDocument, boolean pUseRadiusIndex) {
        Connection connection = MySQLConnexion.getConnexion();
        ResultSet result;

        try {
            PreparedStatement preparedStatment = connection.prepareStatement(DOCUMENT_WORDS);

            int minIndex = pDocument.getMinIndex();
            if (pUseRadiusIndex) {
                minIndex -= DocumentoPDF.INDEX_OFFSET_RADIUS;
                if (minIndex < 0) {
                    minIndex = 0;
                }
            }

            preparedStatment.setInt(1, minIndex);
            preparedStatment.setInt(2, pDocument.getIdDocument());
            if (pUseRadiusIndex) {
                preparedStatment.setInt(3, DocumentoPDF.INDEX_OFFSET_RADIUS * 2);
            } else {
                preparedStatment.setInt(3, DocumentoDB.MAX_WORDS_WITHOUT_INDEX_RADIUS);
            }

            result = preparedStatment.executeQuery();

            StringBuilder fragment = new StringBuilder(100);

            while (result.next()) {
                fragment.append(result.getString("termino"));
                fragment.append(" ");
            }
            fragment.trimToSize();
            pDocument.setDocumentFragment(fragment.toString());

            result.close();
        } catch (Exception e) {
            System.out.println(e.getStackTrace());
        }
    }

    public static HashList getDocuments(DocumentoPDF[] pDocument, Pagination pPagination) {
        int pLength = pPagination.getEndIndex() - pPagination.getInitIndex() + 1;

        HashList hashTable = new HashList(pLength * 4);
        StringBuilder queryItems = new StringBuilder("?");

        for (int i = 0; i < (pLength - 1); i++) {
            queryItems.append(",?");
        }
        String query = DOCUMENTS_GET.replace("?", queryItems.toString());

        Connection connection = MySQLConnexion.getConnexion();
        ResultSet result;
        DocumentoPDF document;

        try {
            PreparedStatement preparedStatment = connection.prepareStatement(query);

            for (int i = 0; i < pLength; i++) {
                preparedStatment.setInt(i + 1, pDocument[pPagination.getInitIndex() + i].getIdDocument());
            }

            result = preparedStatment.executeQuery();

            System.out.println(preparedStatment);

            while (result.next()) {
                document = new DocumentoPDF(result.getInt("idDocumento"),
                        result.getString("nombreDocumento"),
                        result.getString("autor"),
                        result.getString("ruta"),
                        result.getInt("cantidadDePaginas"),
                        result.getInt("maxTF"),
                        result.getInt("tamañoEnBytes"),
                        true);
                hashTable.put(document);
            }
            result.close();
        } catch (Exception e) {
            System.out.println(e.getStackTrace());
        }
        return hashTable;
    }

    /**
     * Devuelve el menor indice de los terminos en cada documento de la lista.
     *
     * @param pDocuments Tabla hash con los documentos y terminos.
     * @param pPagination objeto Pagination
     * @param pQuery Consulta
     * @return una tabla hash con los documentos y los menores indices de los
     * terminos.
     */
    public static HashList getDocumentsMinIndex(HashList pDocuments, Pagination pPagination, SimpleList pQuery) {
        int pLength = pDocuments.size();

        StringBuilder queryItems = new StringBuilder("?");
        for (int i = 0; i < (pLength - 1); i++) {
            queryItems.append(",?");
        }
        String query = GETDOCUMENTS_MIN_INDEX.replace("i?", queryItems.toString());

        queryItems = new StringBuilder("?");
        for (int i = 0; i < pQuery.size() - 1; i++) {
            queryItems.append(",?");
        }

        query = query.replace("w?", queryItems.toString());

        Connection connection = MySQLConnexion.getConnexion();
        ResultSet result;

        DocumentoPDF document;

        try {
            PreparedStatement preparedStatment = connection.prepareStatement(query);

            pDocuments.startIterator();
            int index = 1;
            while (pDocuments.hasNext()) {
                preparedStatment.setInt(index, ((DocumentoPDF) pDocuments.next()).getIdDocument());
                index++;
            }

            pQuery.startIterator();
            while (pQuery.hasNext()) {
                preparedStatment.setString(index, (String) pQuery.next());
                index++;
            }

            result = preparedStatment.executeQuery();

            while (result.next()) {
                document = (DocumentoPDF) pDocuments.get(new DocumentoPDF(result.getInt("idDocumento")));
                document.setMinIndex(result.getInt("indice"));
            }

            result.close();
        } catch (Exception e) {
            System.out.println(e.getStackTrace());
        }
        return pDocuments;
    }
}